package com.muk.meis.model.service.report;

import java.io.StringWriter;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.TransformerFactoryConfigurationError;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;

import com.muk.meis.MEISConstance.DISTRICT;
import com.muk.meis.model.service.BaseMEISService;

public class ReportByOPEC extends BaseMEISService {
	
	public List getOPECReport(DISTRICT district){
		String sql = "select c.districtName, "+
					 "      sum(a.kindergartenMan1), "+
					 "      sum(a.kindergartenGirl1), "+
					 "      sum(a.kindergartenMan2), "+
					 "      sum(a.kindergartenGirl2), "+
					 "      sum(a.kindergartenMan1+a.kindergartenGirl1+a.kindergartenMan2+a.kindergartenGirl2),"+
					 "      sum(a.roomKindergarten1+a.roomKindergarten2), "+
					 
					 "      sum(a.primaryEducationMan1),"+
					 "      sum(a.primaryEducationGirl1),"+
					 "      sum(a.primaryEducationMan2),"+
					 "      sum(a.primaryEducationGirl2),"+
					 "      sum(a.primaryEducationMan3),"+
					 "      sum(a.primaryEducationGirl3),"+
					 "      sum(a.primaryEducationMan4),"+
					 "      sum(a.primaryEducationGirl4),"+
					 "      sum(a.primaryEducationMan5),"+
					 "      sum(a.primaryEducationGirl5),"+
					 "      sum(a.primaryEducationMan6),"+
					 "      sum(a.primaryEducationGirl6),"+
					 "      sum(a.primaryEducationMan1+a.primaryEducationGirl1+a.primaryEducationMan2+a.primaryEducationGirl2+" +
					 "          a.primaryEducationMan3+a.primaryEducationGirl3+a.primaryEducationMan4+a.primaryEducationGirl4+" +
					 "          a.primaryEducationMan5+a.primaryEducationGirl5+a.primaryEducationMan6+a.primaryEducationGirl6)," +
					 "      sum(a.roomPrimaryEducation1+a.roomPrimaryEducation2+a.roomPrimaryEducation3+"+
					 "      a.roomPrimaryEducation4+a.roomPrimaryEducation5+a.roomPrimaryEducation6),"+
					 
					 "      sum(a.highSchoolMan1),"+
					 "      sum(a.highSchoolGirl1),"+
					 "      sum(a.highSchoolMan2),"+
					 "      sum(a.highSchoolGirl2),"+
					 "      sum(a.highSchoolMan3),"+
					 "      sum(a.highSchoolGirl3),"+
					 "      sum(a.highSchoolMan1+a.highSchoolGirl1+a.highSchoolMan2+a.highSchoolGirl2+a.highSchoolMan3+a.highSchoolGirl3),"+
					 "      sum(a.roomHighSchool1+a.roomHighSchool2+a.roomHighSchool3)"+
					 " from School a, SchoolInfo b,  District c"+
					 " where a.percodeCode = b.percodeCode"+
					 "       and b.districtId = c.districtId"+
					 "       and b.districtId = "+district.getCode()+
					 " group by 1";
		List ret = em.createQuery(sql).getResultList();
		return ret;
	}
	
	public String getXMLDataGraph(){
		String sql = "select c.districtName,"+ 
                     " sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"+
                     "    a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+"+
                     "    a.primaryEducationMan5+a.primaryEducationMan6+"+
                     "    a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
                     "    a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
                     "    a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+"+
                     "    a.primaryEducationGirl5+a.primaryEducationGirl6+"+
                     "    a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6)"+ 
                     " from School a, SchoolInfo b, District c"+
                     " where a.percodeCode = b.percodeCode"+
                     "       and b.districtId = c.districtId"+
                     " group by 1";
        try {
            /////////////////////////////
            //Creating an empty XML Document

            //We need a Document
            DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
            Document doc = docBuilder.newDocument();

            ////////////////////////
            //Creating the XML tree
            
            
            //create the root element and add it to the document
            Element root = doc.createElement("pie");
            doc.appendChild(root);

            //create child element, add an attribute, and add to root
            
			List<Object[]> lst = em.createQuery(sql).getResultList();
			for (Object[] obj : lst) {
	            Element child = doc.createElement("slice");
	            child.setAttribute("title", obj[0].toString());
	            Text text = doc.createTextNode(obj[1].toString());
	            child.appendChild(text);
	            root.appendChild(child);
			}

            
            /////////////////
            //Output the XML

            //set up a transformer
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();

            //create string from xml tree
            StringWriter sw = new StringWriter();
            StreamResult result = new StreamResult(sw);
            DOMSource source = new DOMSource(doc);
            trans.transform(source, result);
            String xmlString = sw.toString();

            return xmlString.substring(xmlString.indexOf("?>") + 2).replaceAll("\"", "'");
        } catch (Exception e) {
            System.out.println(e);
        }		
		return null;
	}
	
	public static void main(String[] args) {
		ReportByOPEC obj = new ReportByOPEC();
		System.out.println(obj.getXMLDataGraph());        	
	}

	private static void template() throws TransformerFactoryConfigurationError {
		try {
            /////////////////////////////
            //Creating an empty XML Document

            //We need a Document
            DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
            Document doc = docBuilder.newDocument();

            ////////////////////////
            //Creating the XML tree
            Text text = doc.createTextNode("10");
            
            //create the root element and add it to the document
            Element root = doc.createElement("pie");
            doc.appendChild(root);

            //create child element, add an attribute, and add to root
            Element child = doc.createElement("child");
            child.setAttribute("title", "What is Christmas?");
            child.appendChild(text);
            
            Element test = doc.createElement("test");   
            child.appendChild(test);
            test.appendChild(text); 
            
            root.appendChild(child);

            /////////////////
            //Output the XML

            //set up a transformer
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();
            trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
            trans.setOutputProperty(OutputKeys.INDENT, "yes");

            //create string from xml tree
            StringWriter sw = new StringWriter();
            StreamResult result = new StreamResult(sw);
            DOMSource source = new DOMSource(doc);
            trans.transform(source, result);
            String xmlString = sw.toString();

            //print xml
            System.out.println(xmlString);

        } catch (Exception e) {
            System.out.println(e);
        }
	}

}
